package com.qingke.beSuper2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.codec.digest.DigestUtils;

import com.qingke.beSuper2.pojo.Answer;
import com.qingke.beSuper2.pojo.Player;
import com.qingke.beSuper2.pojo.Question;
import com.qingke.beSuper2.utils.PropertyManager;

public class BeSuperDao {

	public BeSuperDao() {
	}
	
	public Player login(String username, String password) {
		
		String sql = "select id, name, score from besuper.player where username = ? and password = ?";
		
		Connection conn = null;
		PreparedStatement stmt= null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, username);
			stmt.setString(2, DigestUtils.md5Hex(password));
			
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				Player player = new Player();
				long id = rs.getLong("id");
				String name = rs.getString("name");
				int score = rs.getInt("score");
				player.setId(id);
				player.setName(name);
				player.setScore(score);
				
				return player;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
		return null;
	}
	
	public Player signup(String username, String password, String name) {
		
		String sql = "insert into besuper.player(`name`, `username`, `password`) values (?, ?, ?)";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, name);
			stmt.setString(2, username);
			stmt.setString(3, DigestUtils.md5Hex(password));
			
			int affectRows = stmt.executeUpdate();
			
			if (affectRows > 0) {
				return login(username, password);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, null);
		}
		
		return null;
	}
	
	public Player getPlayer(long playerId) {
		String sql = "select id, name, score from player where id = ?";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Player player = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, playerId);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				int score = rs.getInt("score");
				player = new Player(id, name, score);
			}
			return player;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, null);
		}
		
		return null;
	}
	
	public void updatePlayer(int score, long solverId) {
		String sql = "update player set score = ? where id = ?";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, score);
			stmt.setLong(2, solverId);
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
 	public List<Question> searchQuestions() {
		
		List<Question> questions = new ArrayList<Question>();
		String sql = "select id, value, credit, player_id, isOpen from question order by isOpen";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				long id = rs.getLong("id");
				String value = rs.getString("value");
				int credit = rs.getInt("credit");
				long playerId = rs.getLong("player_id");
				boolean isOpen = "Y".equals(rs.getString("isOpen"));
				
				Player player = this.getPlayer(playerId);
				Question question = new Question(id, value, credit, isOpen, player);
				questions.add(question);
			}
			return questions;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, null);
		}
		
		return null;
	}
	
	public List<Question> searchQuestionsBy(Player player) {
		List<Question> questions = new ArrayList<Question>();
		String sql = "select id, value, credit, isOpen from question where player_id = ?";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, player.getId());
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				long id = rs.getLong("id");
				String value = rs.getString("value");
				int credit = rs.getInt("credit");
				boolean isOpen = "Y".equals(rs.getString("isOpen"));
				
				Question question = new Question(id, value, credit, isOpen, player);
				questions.add(question);
			}
			return questions;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, null);
		}
		
		return null;
	}
	
	public List<Question> searchOpenedQuestions() {
		List<Question> questions = new ArrayList<Question>();
		String sql = "select id, value, credit, player_id, isOpen from question where isOpen = 'Y' ";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				long id = rs.getLong("id");
				String value = rs.getString("value");
				int credit = rs.getInt("credit");
				long playerId = rs.getLong("player_id");
				boolean isOpen = "Y".equals(rs.getString("isOpen"));
				
				Player player = this.getPlayer(playerId);
				Question question = new Question(id, value, credit, isOpen, player);
				questions.add(question);
			}
			return questions;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, null);
		}
		
		return null;
	}
	
	public Question searchQuestionsById(long questionId) {
		String sql = "select id, value, credit, player_id, isOpen from question where id = ? ";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, questionId);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				long id = rs.getLong("id");
				String value = rs.getString("value");
				int credit = rs.getInt("credit");
				long playerId = rs.getLong("player_id");
				boolean isOpen = "Y".equals(rs.getString("isOpen"));
				
				Player player = this.getPlayer(playerId);
				Question question = new Question(id, value, credit, isOpen, player);
				return question;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
		return null;
	}
	
	public int insertQuestion(Question question) {
		String sql = "insert into question(value, credit, player_id, isOpen) values(?, ?, ?, ?)";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int affectRows = -1;
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, question.getValue());
			stmt.setInt(2, question.getCredit());
			stmt.setLong(3, question.getFrom().getId());
			stmt.setString(4, question.isOpen() ? "Y" : "N");
			
			affectRows = stmt.executeUpdate();
			
			if (affectRows > 0) {
				return affectRows;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
		return affectRows;
	}
	
	public void updateQuestion(String isOpen) {
		String sql = "update question set isOpen = ?";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, isOpen);
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public List<Answer> searchAnswersByQId(long questionId) {
		String sql = "select id, value, best, player_id from answer where question_id = ?";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Answer> answers = new ArrayList<Answer>();
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, questionId);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				long id = rs.getLong("id");
				String value = rs.getString("value");
				boolean isBest = "Y".equals(rs.getString("best"));
				long playerId = rs.getLong("player_id");
				
				Player replier = this.getPlayer(playerId);
				Question question = this.searchQuestionsById(questionId);
				Answer answer = new Answer(id, value, isBest, replier, question);
				answers.add(answer);
			}
			return answers;
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		return null;
	}
	
	public List<Answer> searchAnswersByPId(long playerId) {
		String sql = "select id, value, best, question_id from answer where player_id = ?";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Answer> answers = new ArrayList<Answer>();
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, playerId);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				long id = rs.getLong("id");
				String value = rs.getString("value");
				boolean isBest = "Y".equals(rs.getString("best"));
				long questionId = rs.getLong("question_id");
				
				Player replier = this.getPlayer(playerId);
				Question question = this.searchQuestionsById(questionId);
				Answer answer = new Answer(id, value, isBest, replier, question);
				answers.add(answer);
			}
			return answers;
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		return null;
	}
	
	public Answer searchAnswer(long answerId) {
		String sql = "select value, best, player_id, question_id from answer where id = ?";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, answerId);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				String value = rs.getString("value");
				boolean isBest = "Y".equals(rs.getString("best"));
				long playerId = rs.getLong("player_id");
				long questionId = rs.getLong("question_id");
				
				Player replier = this.getPlayer(playerId);
				Question question = this.searchQuestionsById(questionId);
				Answer answer = new Answer(answerId, value, isBest, replier, question);
				return answer;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		return null;
	}
	
	public void insertAnswers(String value, long playerId, long questionId) {
		String sql = "insert answer(value, player_id, question_id) valuess(?, ?, ?)";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, value);
			stmt.setLong(2, playerId);
			stmt.setLong(3, questionId);
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void updateAnswer(long answerId, String isBest) {
		String sql = "update answer set best = ? where id = ?";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, isBest);
			stmt.setLong(2, answerId);
			
			stmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, null);
		}
	}
	
	public void acceptAnswer(long questionId, long answerId) {
		updateAnswer(answerId, "Y");
		updateQuestion("N");
	}
	
	public boolean isUsernameExists(String username) {
		String sql = "select username from besuper.player where username = ?";
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, username);
			
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
		return false;
	}
	
	private Connection getConnection() {
		Connection conn = null;
		String url = getProperty("url");
		String user = getProperty("user");
		String password = getProperty("password");
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	private String getProperty(String key) {
		PropertyManager pm = PropertyManager.getInstance("conn.properties");
		return pm.getProperty(key);
	}
	
	private void clean(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
